﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using MySql.Data.MySqlClient;
using System.Data;
using System.Windows.Forms;

namespace TMS
{
    public class Users
    {
        string Name;
        string Surname;
        string Username;
        string ID;
        string Password;
        Int32 Level;
        public bool EditUseres;
        public bool ViewProducts;
        public bool EditProducts;
        public bool ViewWarehouse;
        public bool ViewProjects;
        public bool EditProjects;
        public bool Finance;


        
        public string GetPassword()
        {
            return this.Password;
        }

        public void SetPassword(string Password)
        {
            this.Password = Password;
        }

        public string GetUsername()
        {
            return this.Username;
        }

        public void SetUsername(string Username)
        {
            this.Username = Username;
        }

        public string GetID()
        {
            return this.ID;
        }

        public void SetID(string ID)
        {
            this.ID = ID;
        }

        public string GetName()
        {
            return this.Name;
        }

        public void SetName(string Name)
        {
            this.Name = Name;
        }

        public string GetSurname()
        {
            return this.Surname;
        }

        public void SetSurname(string Surname)
        {
            this.Surname = Surname;
        }

        public Int32 GetLevel()
        {
            return this.Level;
        }

        public void SetLevel(Int32 Level)
        {
            this.Level = Level;
        }

        public void AddUserInDB()
        {
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

            MySqlConnection mysqlCon = new MySqlConnection(connString);

            mysqlCon.Open();

            string stm;

            stm = @"INSERT INTO users VALUES (DEFAULT,'" + this.Name + "','" + this.Surname + "','" + this.Username + "','" + this.Password + "'," + this.Level + "," + Convert.ToInt16(this.EditUseres) + "," + Convert.ToInt16(this.ViewWarehouse) + "," + Convert.ToInt16(this.ViewProducts) + ", " + Convert.ToInt16(this.EditProducts) + "," + Convert.ToInt16(this.ViewProjects) + ", " + Convert.ToInt16(this.EditProjects) + ", " + Convert.ToInt16(this.Finance) + ")";

            MySqlCommand cmd = new MySqlCommand(stm, mysqlCon);
            
            cmd.ExecuteNonQuery();

            mysqlCon.Close();
        }

        public void UpdateUserDB(string name, string surname, string username)
        {
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;
            
            MySqlConnection mysqlCon = new MySqlConnection(connString);

            mysqlCon.Open();

            string stm = "UPDATE users SET Name='" + name + "',Surname='" + surname + "',Username='" + username + "' WHERE ID='" + this.ID + "'";

            MySqlCommand cmd = new MySqlCommand(stm, mysqlCon);
            cmd.ExecuteNonQuery();

            mysqlCon.Close();
        }

        public void UpdateUserDB()
        {
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

            MySqlConnection mysqlCon = new MySqlConnection(connString);

            mysqlCon.Open();

            string stm;

            stm = "UPDATE users SET Name='" + this.Name + 
                "',Surname='" + this.Surname +
                "',Username='" +this.Username +
                "',Password='" + this.Password +
                "',Role=" + this.Level +
                ",EditUserPermition=" + Convert.ToInt16(this.EditUseres) +
                ",ViewWPermition=" + Convert.ToInt16(this.ViewWarehouse) +
                ",ViewProducts=" + Convert.ToInt16(this.ViewProducts) +
                ",EditProducts=" + Convert.ToInt16(this.EditProducts) +
                ",ViewProjects=" + Convert.ToInt16(this.ViewProjects) +
                ",EditProjects=" + Convert.ToInt16(this.EditProjects) +
                ",Finance=" + Convert.ToInt16(this.Finance) +
                " WHERE ID='" + this.ID + "'";
         
            MySqlCommand cmd = new MySqlCommand(stm, mysqlCon);
            cmd.ExecuteNonQuery();

            mysqlCon.Close();
        }

        public void GetUserFromDB()
        {
            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;
           
            MySqlConnection mysqlCon = new MySqlConnection(connString);

            mysqlCon.Open();


            string sqlSelectAll = "SELECT * FROM users WHERE ID='" + this.GetID() + "'";

            MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);
            DataSet UserProfile = new DataSet();
            MyDA.Fill(UserProfile);


            this.Name = UserProfile.Tables[0].Rows[0].ItemArray[1].ToString();
            this.Surname = UserProfile.Tables[0].Rows[0].ItemArray[2].ToString();
            this.Username = UserProfile.Tables[0].Rows[0].ItemArray[3].ToString();
            this.Password = UserProfile.Tables[0].Rows[0].ItemArray[4].ToString();
            this.Level =Convert.ToInt16(UserProfile.Tables[0].Rows[0].ItemArray[5]);
            this.EditUseres = Convert.ToBoolean(UserProfile.Tables[0].Rows[0].ItemArray[6]);
            this.ViewWarehouse = Convert.ToBoolean(UserProfile.Tables[0].Rows[0].ItemArray[7]);
            this.ViewProducts = Convert.ToBoolean(UserProfile.Tables[0].Rows[0].ItemArray[8]);
            this.EditProducts = Convert.ToBoolean(UserProfile.Tables[0].Rows[0].ItemArray[9]);
            this.ViewProjects = Convert.ToBoolean(UserProfile.Tables[0].Rows[0].ItemArray[10]);
            this.EditProjects = Convert.ToBoolean(UserProfile.Tables[0].Rows[0].ItemArray[11]);
            this.Finance = Convert.ToBoolean(UserProfile.Tables[0].Rows[0].ItemArray[12]);


            mysqlCon.Close();
        }

        public void ChangeUserPass(string newPass)
        {
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

            MySqlConnection mysqlCon = new MySqlConnection(connString);

            mysqlCon.Open();

            string stm = "UPDATE users SET Password='" + newPass +  "' WHERE ID='" + this.ID + "'";

            MySqlCommand cmd = new MySqlCommand(stm, mysqlCon);
            cmd.ExecuteNonQuery();

            mysqlCon.Close();
        }

        public void DeleteUserFromDB()
        {
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

            MySqlConnection conn =  new MySqlConnection(connString);
            conn.Open();
            string stm = @"DELETE FROM users WHERE ID='" + this.ID+"'";
            MySqlCommand cmd = new MySqlCommand(stm, conn);
            cmd.ExecuteNonQuery();
        }

        public DataTable GetAllUserNameAndSurname()
        {
            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

            MySqlConnection mysqlCon = new MySqlConnection(connString);

            mysqlCon.Open();


            string sqlSelectAll = "SELECT Name,Surname FROM users";

            MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);
            DataSet UserProfile = new DataSet();
            MyDA.Fill(UserProfile);
            DataTable table = new DataTable();
            table.Columns.Add();
            //table.Columns[0].ColumnName = "";
            foreach (DataRow row in UserProfile.Tables[0].Rows)
            {
                table.Rows.Add(new object[] {row.ItemArray[0].ToString()+" "+ row.ItemArray[1].ToString()});
            }
            return table;
            
        }
    }
}
